﻿Imports System.IO
Imports System.Data.SqlClient
Imports System.Data
Imports System.Web.UI.DataVisualization.Charting

Partial Class Reportes_RPTHogaresPersonasXQuinquenio1
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If Page.IsPostBack = False Then
            'cargar proyectos
            llenarCombos()
        End If


    End Sub


    Protected Sub btnConsultar_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnConsultar.Click
        cargardatos()
        generarGrafica()
    End Sub
    Sub cargardatos()
        Using cnn As New SqlConnection(ConfigurationManager.ConnectionStrings("CNNdb").ConnectionString)
            cnn.Open()

            Using cmd As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand("usp_RPTEducacionAgrupado", cnn)
                cmd.CommandType = CommandType.StoredProcedure
                cmd.Parameters.AddWithValue("@campo", "Quinquenio")
                cmd.Parameters.AddWithValue("@orden", ddlOrden.SelectedValue)
                cmd.Parameters.AddWithValue("@desAsc", ddldesAsc.SelectedValue)
                cmd.Parameters.AddWithValue("@codigoProyecto", ddlProyectos.SelectedValue)
                cmd.Parameters.AddWithValue("@codMunicipio", ddlMunicipio.SelectedValue)
                cmd.Parameters.AddWithValue("@Visita", ddlVisita.SelectedValue)

                grdResultado.DataSource = cmd.ExecuteReader
                grdResultado.DataBind()
                'Dim titulo As String = "<h2> Reporte por " & ddlVariable.SelectedItem.Text & "<br/>Fecha:" & Date.Now.ToShortDateString & "<h2>"
                'lblTitulo.Text = titulo
            End Using

            cnn.Close()

        End Using
    End Sub
    Protected Sub btnExpt_Click(ByVal sender As Object, ByVal e As System.EventArgs) 'Handles btnExpt.Click

    End Sub

    Protected Sub exportarAExcel()


        Dim sb As StringBuilder = New StringBuilder()
        Dim sw As StringWriter = New StringWriter(sb)
        Dim htw As HtmlTextWriter = New HtmlTextWriter(sw)
        Dim pagina As Page = New Page

        Dim form = New HtmlForm
        pagina.EnableEventValidation = False
        pagina.DesignerInitialize()
        pagina.Controls.Add(form)

        'form.Controls.Add(tabla)
        form.Controls.Add(Me.grdResultado)
        pagina.RenderControl(htw)
        Response.Clear()
        Response.Buffer = True
        Response.ContentType = "application/vnd.ms-excel"
        Response.AddHeader("Content-Disposition", "attachment;filename=Reporte.xls")
        Response.Charset = "UTF-8"
        Response.ContentEncoding = Encoding.Default
        Response.Write(sb.ToString())
        Response.End()

    End Sub

    Protected Sub btnExp_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnExp.Click
        cargardatos()
        exportarAExcel()
    End Sub

    Sub llenarCombos()
        Dim u As New Web_Usuario
        Using h As New Helper
            u = h.GetUsuario(Context.User.Identity.Name)
        End Using
        Using bd As New BdHogaresDataContext
            Dim a = From i In bd.Web_Proyectos
                    Where (i.web_Operadore.idOperador = u.IdOperador) Or IsNothing(u.IdOperador)
                    Select i.idProyecto, i.CodigoProyecto
            ddlProyectos.DataSource = a
            ddlProyectos.DataValueField = "CodigoProyecto"
            ddlProyectos.DataTextField = "CodigoProyecto"
            ddlProyectos.DataBind()
            ddlProyectos.Items.Insert(0, New ListItem("Todos", "0"))

            Dim b = From i In bd.Web_Parametros
                    Where (i.Categoria = "visitas")
                    Select i.NombreOpcion, i.ValorOpcion

            ddlVisita.DataSource = b
            ddlVisita.DataValueField = "NombreOpcion"
            ddlVisita.DataTextField = "NombreOpcion"
            ddlVisita.DataBind()

        End Using
        Using bd As New bdReportesDataContext

            ddlMunicipio.DataSource = bd.usp_MunicipiosEstrategia(Context.User.Identity.Name)
            ddlMunicipio.DataValueField = "codMunicipio"
            ddlMunicipio.DataTextField = "Municipio"
            ddlMunicipio.DataBind()
            ddlMunicipio.Items.Insert(0, New ListItem("Todos", "0"))
        End Using


        'Using cnn As New SqlConnection(ConfigurationManager.ConnectionStrings("CNNdb").ConnectionString)
        '    cnn.Open()
        '    LlenarItems(ddlVariable, "RPTEducacionAgrupado", cnn)
        '    cnn.Close()

        'End Using
    End Sub

    Sub LlenarItems(ByVal objeto As Object, ByVal pregunta As String, ByRef cnn As SqlConnection)
        If pregunta = "sino" Then : objeto.Items.Add(New ListItem("Sí", 1)) : objeto.Items.Add(New ListItem("No", 2)) : Exit Sub : End If
        Using drDatos As SqlDataReader = Funciones.TraerListas(pregunta, cnn)
            With objeto
                .DataSource = drDatos
                .DataTextField = "nombreopcion"
                .DataValueField = "valoropcion"
                .DataBind()
            End With
        End Using
    End Sub
    Sub generarGrafica()

        Using cnn As New SqlConnection(ConfigurationManager.ConnectionStrings("CNNdb").ConnectionString)
            cnn.Open()

            Using cmd2 As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand("usp_RPTEducacionAgrupado", cnn)
                cmd2.CommandType = CommandType.StoredProcedure
                cmd2.Parameters.AddWithValue("@campo", "Quinquenio")
                cmd2.Parameters.AddWithValue("@orden", ddlOrden.SelectedValue)
                cmd2.Parameters.AddWithValue("@desAsc", ddldesAsc.SelectedValue)
                cmd2.Parameters.AddWithValue("@codigoProyecto", ddlProyectos.SelectedValue)
                cmd2.Parameters.AddWithValue("@codMunicipio", ddlMunicipio.SelectedValue)
                cmd2.Parameters.AddWithValue("@Visita", ddlVisita.SelectedValue)
                cmd2.Parameters.AddWithValue("@grafica", 1)

                With Chart1
                    .DataSource = cmd2.ExecuteReader
                    .ImageType = DataVisualization.Charting.ChartImageType.Png
                    .ChartAreas("ChartArea1").AxisY.LabelStyle.Format = "N0"
                    .ChartAreas("ChartArea1").Area3DStyle.Enable3D = True
                    .ChartAreas("ChartArea1").Area3DStyle.Enable3D = False

                    .Legends("Default").LegendStyle = LegendStyle.Table
                    .Legends("Default").Enabled = True

                    .Titles("Title1").Text = "Total de personas por quinquenio"


                    With .Series("Default")
                        .XValueMember = "Detalle"
                        .YValueMembers = "total"
                        .ChartType = rbTipoGrafica1.SelectedValue
                        .LabelFormat = "N0"
                        .IsVisibleInLegend = True
                        .Item("PointWidth") = "0.6"
                        .IsValueShownAsLabel = True
                        .Item("BarLabelStyle") = "Center"
                        .Item("DrawingStyle") = "Cylinder"
                        .Name = "Ciclo vital"
                    End With

                    .DataBind()

                End With
                'With Chart1
                '    .DataSource = cmd2.ExecuteReader
                '    .ImageType = DataVisualization.Charting.ChartImageType.Png
                '    .Series("Default").XValueMember = "Detalle"
                '    .Series("Default").YValueMembers = "total"
                '    .DataBind()

                '    .Series("Default").ChartType = rbTipoGrafica1.SelectedValue
                '    .Series("Default").LabelFormat = "N0"
                '    .ChartAreas("ChartArea1").AxisY.LabelStyle.Format = "N0"
                '    .Series("Default")("PointWidth") = "0.6"
                '    .Series("Default").IsValueShownAsLabel = False
                '    .Series("Default")("BarLabelStyle") = "Center"
                '    .ChartAreas("ChartArea1").Area3DStyle.Enable3D = True
                '    .Series("Default")("DrawingStyle") = "Cylinder"
                '    .ChartAreas("ChartArea1").Area3DStyle.Enable3D = False
                '    .Titles("Title1").Text = "Total de personas de acuerdo a ciclo vital"
                'End With
            End Using
            cnn.Close()
            cnn.Open()
            Using cmd2 As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand("usp_RPTEducacionAgrupado", cnn)
                cmd2.CommandType = CommandType.StoredProcedure
                cmd2.Parameters.AddWithValue("@campo", "Quinquenio")
                cmd2.Parameters.AddWithValue("@orden", ddlOrden.SelectedValue)
                cmd2.Parameters.AddWithValue("@desAsc", ddldesAsc.SelectedValue)
                cmd2.Parameters.AddWithValue("@codigoProyecto", ddlProyectos.SelectedValue)
                cmd2.Parameters.AddWithValue("@codMunicipio", ddlMunicipio.SelectedValue)
                cmd2.Parameters.AddWithValue("@Visita", ddlVisita.SelectedValue)
                cmd2.Parameters.AddWithValue("@grafica", 1)

                With Chart2
                    .DataSource = cmd2.ExecuteReader
                    .ImageType = DataVisualization.Charting.ChartImageType.Png
                    .ChartAreas("ChartArea1").AxisY.LabelStyle.Format = "N0"
                    .ChartAreas("ChartArea1").Area3DStyle.Enable3D = True
                    .ChartAreas("ChartArea1").Area3DStyle.Enable3D = False

                    .Legends("Default").LegendStyle = LegendStyle.Table
                    .Legends("Default").Enabled = True

                    .Titles("Title1").Text = "Total de personas por quinquenio"

                    With .Series("Default")
                        .XValueMember = "Detalle"
                        .YValueMembers = "Femenino"
                        .ChartType = rbTipoGrafica2.SelectedValue
                        .LabelFormat = "N0"
                        .IsVisibleInLegend = True
                        .Item("PointWidth") = "0.6"
                        .IsValueShownAsLabel = True
                        .Item("BarLabelStyle") = "Center"
                        .Item("DrawingStyle") = "Cylinder"
                        .Name = "Mujeres"
                    End With

                    With .Series("Default2")
                        .XValueMember = "Detalle"
                        .YValueMembers = "Masculino"
                        .ChartType = rbTipoGrafica2.SelectedValue
                        .LabelFormat = "N0"
                        .IsVisibleInLegend = True
                        .Item("PointWidth") = "0.6"
                        .IsValueShownAsLabel = True
                        .Item("BarLabelStyle") = "Center"
                        .Item("DrawingStyle") = "Cylinder"
                        .Name = "Hombres"
                    End With

                    .DataBind()

                End With

                'With Chart2
                '    .Legends("Default").LegendStyle = LegendStyle.Table
                '    .Legends("Default").Enabled = True
                '    .DataSource = cmd2.ExecuteReader
                '    .ImageType = DataVisualization.Charting.ChartImageType.Png
                '    .Series("Default").XValueMember = "Detalle"
                '    .Series("Default").YValueMembers = "Femenino"
                '    .Series("Default2").XValueMember = "Detalle"
                '    .Series("Default2").YValueMembers = "Masculino"
                '    '.Series("Default2").Legend = "Masculino"

                '    .DataBind()

                '    .Series("Default").ChartType = rbTipoGrafica2.SelectedValue
                '    .Series("Default").LabelFormat = "N0"
                '    .Series("Default").IsValueShownAsLabel = False
                '    .Series("Default")("BarLabelStyle") = "Center"
                '    .Series("Default")("DrawingStyle") = "Cylinder"
                '    .Series("Default2").ChartType = rbTipoGrafica2.SelectedValue
                '    .Series("Default2").LabelFormat = "N0"
                '    .Series("Default2").IsValueShownAsLabel = False
                '    .Series("Default2")("BarLabelStyle") = "Center"
                '    .Series("Default2")("DrawingStyle") = "Cylinder"


                '    .ChartAreas("ChartArea1").AxisY.LabelStyle.Format = "N0"
                '    .ChartAreas("ChartArea1").Area3DStyle.Enable3D = True
                '    .ChartAreas("ChartArea1").Area3DStyle.Enable3D = False
                '    .Titles("Title1").Text = "Total de personas de acuerdo a ciclo vital por sexo"
                'End With
            End Using

        End Using


        Using cnn As New SqlConnection(ConfigurationManager.ConnectionStrings("CNNdb").ConnectionString)
            cnn.Open()

            Using cmd As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand("RPTHogaresPersonasXquinquenioGraf", cnn)
                cmd.CommandType = CommandType.StoredProcedure
                cmd.Parameters.AddWithValue("@codProyecto", ddlProyectos.SelectedValue)
                cmd.Parameters.AddWithValue("@codMunicipio", ddlMunicipio.SelectedValue)
                cmd.Parameters.AddWithValue("@Visita", ddlVisita.SelectedValue)
                cmd.Parameters.AddWithValue("@depto", "0")
                cmd.Parameters.AddWithValue("@localizacion", "0")

                Dim reader As IDataReader = cmd.ExecuteReader()
                
                'With Chart1
                '    .DataSource = reader
                '    .ImageType = DataVisualization.Charting.ChartImageType.Png
                '    .Series("Default").XValueMember = "Detalle"
                '    .Series("Default").YValueMembers = "Cantidad"
                '    .DataBind()

                '    .Series("Default").ChartType = rbTipoGrafica1.SelectedValue
                '    .Series("Default").LabelFormat = "N0"
                '    .ChartAreas("ChartArea1").AxisY.LabelStyle.Format = "N0"
                '    .Series("Default")("PointWidth") = "0.6"
                '    .Series("Default").IsValueShownAsLabel = False
                '    .Series("Default")("BarLabelStyle") = "Center"
                '    .ChartAreas("ChartArea1").Area3DStyle.Enable3D = True
                '    .Series("Default")("DrawingStyle") = "Cylinder"
                '    .ChartAreas("ChartArea1").Area3DStyle.Enable3D = False
                'End With

                reader.NextResult()

                'With Chart2
                '    .DataSource = reader
                '    .ImageType = DataVisualization.Charting.ChartImageType.Png
                '    .Series("Default").XValueMember = "Quinquenio"
                '    .Series("Default").YValueMembers = "mujeres"
                '    .DataBind()

                '    .Series("Default").ChartType = RadioButtonList1.SelectedValue
                '    .Series("Default").LabelFormat = "N0"
                '    .ChartAreas("ChartArea1").AxisY.LabelStyle.Format = "N0"
                '    .Series("Default")("PointWidth") = "0.6"
                '    .Series("Default").IsValueShownAsLabel = False
                '    .Series("Default")("BarLabelStyle") = "Center"
                '    .ChartAreas("ChartArea1").Area3DStyle.Enable3D = True
                '    .Series("Default")("DrawingStyle") = "Cylinder"
                '    .ChartAreas("ChartArea1").Area3DStyle.Enable3D = False
                'End With

                reader.NextResult()

                'With Chart3
                '    .DataSource = reader
                '    .ImageType = DataVisualization.Charting.ChartImageType.Png
                '    .Series("Default").XValueMember = "Quinquenio"
                '    .Series("Default").YValueMembers = "hombres"
                '    .DataBind()

                '    .Series("Default").ChartType = RadioButtonList2.SelectedValue
                '    .Series("Default").LabelFormat = "N0"
                '    .ChartAreas("ChartArea1").AxisY.LabelStyle.Format = "N0"
                '    .Series("Default")("PointWidth") = "0.6"
                '    .Series("Default").IsValueShownAsLabel = False
                '    .Series("Default")("BarLabelStyle") = "Center"
                '    .ChartAreas("ChartArea1").Area3DStyle.Enable3D = True
                '    .Series("Default")("DrawingStyle") = "Cylinder"
                '    .ChartAreas("ChartArea1").Area3DStyle.Enable3D = False
                'End With

                reader.NextResult()

                With Chart4
                    .DataSource = reader
                    .ImageType = DataVisualization.Charting.ChartImageType.Bmp
                    .Series("Default").XValueMember = "Quinquenio"
                    .Series("Default").YValueMembers = "Hombres"
                    .Series("Default2").XValueMember = "Quinquenio"
                    .Series("Default2").YValueMembers = "Mujeres"
                    '.Series("Default2").Legend = "Masculino"

                    .DataBind()

                    .Series("Default").ChartType = 7
                    .Series("Default").LabelFormat = "N0"
                    .Series("Default").IsValueShownAsLabel = False
                    .Series("Default")("BarLabelStyle") = "Center"
                    .Series("Default")("DrawingStyle") = "Cylinder"
                    .Series("Default2").ChartType = 7
                    .Series("Default2").LabelFormat = "N0"
                    .Series("Default2").IsValueShownAsLabel = False
                    .Series("Default2")("BarLabelStyle") = "Center"
                    .Series("Default2")("DrawingStyle") = "Cylinder"

                    .ChartAreas("ChartArea1").AxisX.IsLabelAutoFit = True
                    .ChartAreas("ChartArea1").AxisX.IntervalAutoMode = IntervalAutoMode.VariableCount
                    .ChartAreas("ChartArea1").AxisY.LabelStyle.Format = "N0"
                    .ChartAreas("ChartArea1").Area3DStyle.Enable3D = True
                    .ChartAreas("ChartArea1").Area3DStyle.Enable3D = False


                    .Series("Default").Name = "Mujeres"
                    .Series("Default2").Name = "Hombres"

                End With


            End Using
            cnn.Close()



        End Using



    End Sub

    'Protected Sub Chart2_CustomizeLegend(ByVal sender As Object, ByVal e As System.Web.UI.DataVisualization.Charting.CustomizeLegendEventArgs) Handles Chart2.CustomizeLegend
    '    ' Loop through all default legend items 
    '    If e.LegendName = "Default" Then
    '        For Each item As LegendItem In e.LegendItems
    '            ' Check item series name 
    '            If item.SeriesName = "Default" Then
    '                item.Cells(1).Text = "Femenino"
    '            End If
    '            If item.SeriesName = "Default2" Then
    '                item.Cells(1).Text = "Masculino"
    '            End If


    '        Next
    '    End If

    'End Sub

    Protected Sub rbTipoGrafica1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles rbTipoGrafica1.SelectedIndexChanged
        generarGrafica()
    End Sub

    Protected Sub rbTipoGrafica2_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles rbTipoGrafica2.SelectedIndexChanged
        generarGrafica()
    End Sub
End Class
